package cn.kgc.dao.impl;


import cn.kgc.dao.BaseDao;
import cn.kgc.dao.NewsDao;
import cn.kgc.pojo.News;
import cn.kgc.pojo.NewsCategory;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsDaoImpl extends BaseDao implements NewsDao{
    protected int i = 0;

    //通过数据源获取数据库连接，进行数据库操作，查询新闻
    public void getNewsListByDS() {
        try {
            String sql = "SELECT id,categoryid,title,summary,content,author,createdate FROM news_detail";
            Object[] params = {};
            rs = this.executeQuery2(sql,params);
            while (rs.next()){
                int id = rs.getInt("id");
                int categoryid = rs.getInt("categoryid");
                String newsTitle = rs.getString("title");
                String summary = rs.getString("summary");
                String content = rs.getString("content");
                String author = rs.getString("author");
                Timestamp createdate = rs.getTimestamp("createdate");
                System.out.println(id+"\t"+categoryid+"\t"+newsTitle+"\t"+summary+"\t"+content+"\t"+author+"\t"+createdate.toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }

    }

    //增加新闻信息
    public boolean addNews(News news){
        boolean flag = false;
        if (this.getConnection()){
            try {
                String sql = "INSERT INTO news_detail(categoryid,title,summary,content,author,createdate,picpath)\n" +
                        "VALUES(?,?,?,?,?,?,?);";
                Object[] params = {news.getCategoryId(),news.getTitle(),news.getSummary(),news.getContent(),
                        news.getAuthor(),news.getCreateDate(),news.getPicPath()};
                int i = this.executeUpdate(sql, params);
                if (i>0){
                    System.out.println("插入新闻成功！");
                    flag=true;
                }else {
                    System.out.println("插入新闻失败！");
                }
            }catch (Exception e) {
                e.printStackTrace();
            } finally {
                closeResource();
            }
        }
        return flag;
    }

    //删除特定新闻的方法
    public boolean deleteNews(News news){
        boolean flag = false;
        try {
            String sql = "DELETE FROM news_detail WHERE id=?";
            Object[] params = {news.getId()};
            i = this.executeUpdate(sql, params);
            if (i>0) {
                System.out.println("删除新闻成功！");
                flag=true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return flag;
    }

    //修改特定标题的方法
    public boolean updateNewsTitle(News news){
        boolean flag = false;
        try {
            String sql = "UPDATE news_detail SET title=? WHERE id=?";
            Object[] params = {news.getTitle(),news.getId()};
            i = this.executeUpdate(sql, params);
            if (i>0) {
                System.out.println("修改新闻标题成功！");
                flag=true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return flag;
    }

    //修改特定新闻的方法
    public boolean updateNews(News news){
        boolean flag = false;
        try {
            String sql = "UPDATE news_detail SET categoryid=?,title=?,summary=?,content=?,picpath=?,author=?,modifyDate=? WHERE id=?";
            Object[] params = {news.getCategoryId(),news.getTitle(),news.getSummary(),news.getContent(),news.getPicPath()
                    ,news.getAuthor(),news.getModifyDate(),news.getId()};
            i = this.executeUpdate(sql, params);
            System.out.println("i: "+ i);
            System.out.print("id: "+news.getId()+"\t");
            System.out.print("categoryid: "+news.getCategoryId()+"\t");
            System.out.print("title: "+news.getTitle()+"\t");
            System.out.print("summary: "+news.getSummary()+"\t");
            System.out.print("content: "+news.getContent()+"\t");
            System.out.print("picpath: "+news.getPicPath()+"\t");
            System.out.print("author: "+news.getAuthor()+"\t");
            System.out.println("modifyDate: "+news.getModifyDate());
            if (i>0) {
                System.out.println("修改新闻成功！");
                flag=true;
            }else {
                System.out.println("修改新闻失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return flag;
    }

    //查询全部新闻信息
    public List<News> getNewsList(){
        List<News> list = new ArrayList<News>();
        try {
            String sql = "SELECT id,categoryid,title,summary,content,author,createdate FROM news_detail";
            Object[] params = {};
            rs = this.executeQuery(sql,params);
            while (rs.next()){
                int id = rs.getInt("id");
                int categoryid = rs.getInt("categoryid");
                String newsTitle = rs.getString("title");
                String summary = rs.getString("summary");
                String content = rs.getString("content");
                String author = rs.getString("author");
                Timestamp createDate = rs.getTimestamp("createdate");

//                System.out.println(id+"\t"+categoryid+"\t"+newsTitle+"\t"+summary+"\t"+content+"\t"+author+"\t"+createdate.toString());

                News news = new News();

                news.setId(id);
                news.setTitle(newsTitle);
                news.setCategoryId(categoryid);
                news.setSummary(summary);
                news.setContent(content);
                news.setAuthor(author);
                news.setCreateDate(createDate);

                list.add(news);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return list;
    }
    //查询特定标题的新闻信息
    public void getNewsByTitle(News news){

        try {
            String sql = "SELECT id,title FROM news_detail where title=?";
            Object[] params = {news.getTitle()};
            rs = this.executeQuery(sql,params);

            while (rs.next()){
                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                System.out.println(news.getId()+"\t"+news.getTitle());
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
           closeResource();
        }
    }

    //查询某个新闻分类下是否有新闻
    public int getCountByCategory(NewsCategory newsCategory) {
        int count = 0;
        String sql = "select count(1) from news_detail where categoryid=?";
        Object[] params = {newsCategory.getId()};
        if (this.getConnection()){
            try {
                ResultSet rs = this.executeQuery(sql,params);
                while (rs.next()){
                    count = rs.getInt(1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                this.closeResource();
            }
        }
        return count;
    }

    public News getNewsListById(int id) {
        News news = new News();
        String sql = "select id,categoryid,title,summary,author,content,createdate,picpath from news_detail where id=?";
        Object[] params = {id};
        if (this.getConnection()){
            try {
                ResultSet rs = this.executeQuery(sql,params);
                while (rs.next()){
                    int newId = rs.getInt("id");
                    int categoryid = rs.getInt("categoryid");
                    String title = rs.getString("title");
                    String summary = rs.getString("summary");
                    String content = rs.getString("content");
                    String author = rs.getString("author");
                    Timestamp createDate = rs.getTimestamp("createdate");
                    String picpath = rs.getString("picpath");

                    news.setId(newId);
                    news.setCategoryId(categoryid);
                    news.setTitle(title);
                    news.setSummary(summary);
                    news.setContent(content);
                    news.setAuthor(author);
                    news.setCreateDate(createDate);
                    news.setPicPath(picpath);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                this.closeResource();
            }
        }
        return news;
    }

    public int getTotalCount() {
        int total = 0;
        String sql = "SELECT  COUNT(1) FROM news_detail";
        Object[] params = {};
        if (this.getConnection()){
            try {
                ResultSet rs = this.executeQuery(sql,params);
                while (rs.next()){
                    total = rs.getInt(1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                this.closeResource();
            }
        }

        return total;
    }

    //分页获取新闻数据：pageNo 当前页码 pageSize 页面容量
    public List<News> getPageNewsList(int pageNo,int pageSize) {
        List<News> list = new ArrayList<News>();
        try {
            String sql =
                    "SELECT id,title,author,createdate FROM news_detail WHERE 1=1 order by createdate desc LIMIT ?,?;";
            Object[] params = {(pageNo-1)*pageSize,pageSize};
            rs = this.executeQuery(sql,params);
            while (rs.next()){
                int id = rs.getInt("id");
                String newsTitle = rs.getString("title");
                String author = rs.getString("author");
                Timestamp createDate = rs.getTimestamp("createdate");

                News news = new News();

                news.setId(id);
                news.setTitle(newsTitle);
                news.setAuthor(author);
                news.setCreateDate(createDate);

                list.add(news);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return list;
    }


    public static void main(String[] args) {
        NewsDaoImpl newsDaoImpl = new NewsDaoImpl();
//        News news = new News();

//        news.setTitle("Java Web开课啦");
//        newsDaoImpl.getNewsByTitle(news);

//        news.setId(99);
//        news.setCategoryId(1);
//        news.setTitle("test");
//        news.setContent("test");
//        news.setAuthor("tom");
//        news.setCreateDate(new Date());
//        newsDaoImpl.addNews(news);
//        newsDaoImpl.getNewsList();

//        news.setId(3);
//        newsDaoImpl.deleteNews(news);
//        newsDaoImpl.getNewsList();

//        news.setId(4);
//        news.setTitle("newTitle");
//        newsDaoImpl.updateNews(news);
//        newsDaoImpl.getNewsList();

//        news.setId(99);
//        newsDaoImpl.deleteNews(news);
        List<News> list = newsDaoImpl.getNewsList();
        for (News news : list) {
            System.out.print(news.getId()+"\t");
            System.out.print(news.getTitle()+"\n");
        }
    }
}
